/*
 * Copyright (C) 2009 Dimitrios C. Michalakos
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.cs.util.db;

import com.cs.util.db.connection.ConnectionFactory;
import com.cs.util.db.query.QueryException;
import com.cs.util.db.query.RestfulQuery;
import com.cs.util.db.ioc.IOController;
import com.cs.util.db.ioc.MySqlBigInt;
import com.cs.util.db.ioc.MySqlBinary;
import com.cs.util.db.ioc.MySqlBit;
import com.cs.util.db.ioc.MySqlBlob;
import com.cs.util.db.ioc.MySqlChar;
import com.cs.util.db.ioc.MySqlDate;
import com.cs.util.db.ioc.MySqlDateTime;
import com.cs.util.db.ioc.MySqlDecimal;
import com.cs.util.db.ioc.MySqlDouble;
import com.cs.util.db.ioc.MySqlEnum;
import com.cs.util.db.ioc.MySqlFloat;
import com.cs.util.db.ioc.MySqlInt;
import com.cs.util.db.ioc.MySqlLongBlob;
import com.cs.util.db.ioc.MySqlLongText;
import com.cs.util.db.ioc.MySqlMediumBlob;
import com.cs.util.db.ioc.MySqlMediumInt;
import com.cs.util.db.ioc.MySqlMediumText;
import com.cs.util.db.ioc.MySqlSet;
import com.cs.util.db.ioc.MySqlSmallInt;
import com.cs.util.db.ioc.MySqlText;
import com.cs.util.db.ioc.MySqlTime;
import com.cs.util.db.ioc.MySqlTimeStamp;
import com.cs.util.db.ioc.MySqlTinyInt;
import com.cs.util.db.ioc.MySqlTinyText;
import com.cs.util.db.ioc.MySqlVarBinary;
import com.cs.util.db.ioc.MySqlVarChar;
import com.cs.util.db.ioc.MySqlYear;
import com.cs.util.db.representation.XhtmlForm;
import com.cs.util.db.representation.XhtmlTable;
import com.cs.util.db.sql.Filter;
import com.cs.util.db.ioc.IOCException;
import com.cs.util.db.query.MultipartFormData;
import com.cs.util.db.representation.RepresentationException;
import com.cs.util.db.sql.MySqlDesigner;
import com.cs.util.db.sql.SqlDesigner;
import com.cs.util.http.QueryString;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import javax.mail.internet.MimeMultipart;

/**
 * Database object can be used to easily get or set io from a designated database.
 * The term database in this case refers to six (6) attributes:
 * 1. type, i.e. MySql
 * 2. host, i.e. 167.200.187.200 or my-server.com
 * 3. port, i.e. 3306
 * 4. schema, i.e. logistics
 * 5. username
 * 6. password
 * @author dimitris@jmike.gr
 */
public class Database {

    private final Connection conn;
    private final DatabaseEngineEnum engine;
    private final String schema;
    private DatabaseMetaInfo databaseMeta = null;
    private Map<String, ResultSetMetaInfo> resultSetMeta = new HashMap<String, ResultSetMetaInfo>();

    /**
     * Constructs a new Database object.
     * Opens a connection based on the specified parameters.
     * @param engine the database's software engine (i.e. MYSQL or MSSQL).
     * @param host the database's host.
     * @param port the database's port.
     * @param schema the database's schema.
     * @param username the username that can be used to connect to the database.
     * @param password the password that can be used to connect to the database.
     * @throws SQLException
     */
    public Database(DatabaseEngineEnum engine, String host, int port, String schema, String username, String password) throws SQLException {
        try {
            this.conn = ConnectionFactory.getConnection(engine, host, port, schema, username, password);
            this.engine = engine;
            this.schema = schema;
        } catch (ClassNotFoundException ex) {
            final SqlExceptionState state = SqlExceptionState.DATABASE_NOT_CONNECTED;
            throw new SQLException(ex.getMessage(), state.name(), state.code());
        }
    }

    /**
     * Closes the database.
     * Closes the connection to the database.
     * If the database is already closed, this function does nothing.
     * @throws SQLException
     */
    public void close() throws SQLException {
        if (conn != null) {//connection not null, thus a connection to database has been opened
            conn.close();
            databaseMeta = null;
            resultSetMeta.clear();
        }
    }

    /**
     * Returns the database's schema.
     * @return
     */
    public String getSchemaName() {
        return schema;
    }

    /**
     * Returns the database's software engine type.
     * @return
     */
    public DatabaseEngineEnum getEngineType() {
        return engine;
    }

    /**
     * Retrieves a JDBC DatabaseMetaData object that contains metadata about the database.
     * @return DatabaseMetaData object.
     * @throws SQLException
     */
    public DatabaseMetaData getDatabaseMetaData() throws SQLException {
        return conn.getMetaData();
    }

    /**
     * Creates a Statement object for sending SQL statements to the database.
     * @return a new default Statement object .
     * @throws SQLException
     */
    public Statement createStatement() throws SQLException {
        return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create statement
    }

    /**
     * Creates a Statement object for sending SQL statements to the database.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return new Statement object that will generate ResultSet objects with the given type and concurrency.
     * @throws SQLException
     */
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.createStatement(resultSetType, resultSetConcurrency);//Create statement
    }

    /**
     * Creates a CallableStatement object for calling database stored procedures.
     * @param sql a SQL statement that may contain one or more '?' parameter placeholders.
     * @return a new CallableStatement object containing the pre-compiled SQL statement.
     * @throws SQLException
     */
    public CallableStatement getCallableStatement(String sql) throws SQLException {
        return conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create callable statement
    }

    /**
     * Creates a CallableStatement object for calling database stored procedures.
     * @param sql a SQL statement that may contain one or more '?' parameter placeholders.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return a new CallableStatement object containing the pre-compiled SQL statement.
     * @throws SQLException
     */
    public CallableStatement getCallableStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.prepareCall(sql, resultSetType, resultSetConcurrency);//Create callable statement
    }

    /**
     * Creates a PreparedStatement object for sending parameterized SQL statements to the database.
     * @param sql a SQL statement that may contain one or more '?' IN parameter placeholders.
     * @return A new PreparedStatement object containing the pre-compiled SQL statement.
     * @throws SQLException
     */
    public PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create prepared statement
    }

    /**
     * Creates a PreparedStatement object for sending parameterized SQL statements to the database.
     * @param sql a SQL statement that may contain one or more '?' IN parameter placeholders.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return A new PreparedStatement object containing the pre-compiled SQL statement.
     * @throws SQLException
     */
    public PreparedStatement getPreparedStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.prepareStatement(sql, resultSetType, resultSetConcurrency);//Create prepared statement
    }

    /**
     * Executes the given SQL statement and returns a single ResultSet object.
     * @param sql a SQL statement.
     * @return a new Resultset object.
     * @throws SQLException
     */
    public ResultSet getResultSet(String sql) throws SQLException {
        return this.getResultSet(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    }

    /**
     * Executes the given SQL statement and returns a single ResultSet object.
     * @param sql a SQL statement.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return a new Resultset object.
     * @throws SQLException
     */
    public ResultSet getResultSet(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        Statement stmt = conn.createStatement(resultSetType, resultSetConcurrency);//create statement
        return stmt.executeQuery(sql);//execute sql - get resultset
    }

    /**
     * Executes the given SQL statement and returns a ResultSetMetaData object.
     * @param sql a SQL statement.
     * @return a new ResultSetMetaData object.
     * @throws SQLException
     */
    public ResultSetMetaData getResultSetMetaData(String sql) throws SQLException {
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);//create statement
        ResultSet rs = this.getResultSet(sql);
        ResultSetMetaData rsmd = rs.getMetaData();//get ResultSetMetaData from resultset
        rs.close();//close resultset
        stmt.close();//close statement
        return rsmd;//return ResultSetMetaData
    }

    /**
     * Returns a DatabaseMetaInfo object containing information about the types and properties of the tables in this database.
     * @return a new DatabaseMetaInfo object.
     * @throws SQLException
     */
    public DatabaseMetaInfo getDatabaseMetaInfo() throws SQLException {
        if (this.databaseMeta == null) {//tableInfo does not exist in memory
            /* calculate tableInfo */
            switch (this.engine) {
                case MYSQL: {
                    this.databaseMeta = new MySqlDatabaseMetaInfo(this);
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return this.databaseMeta;//return tableInfo from memory
    }

    /**
     * Returns a ResultSetMetaInfo object containing information about the types and properties of the the designated table's columns.
     * @param table the name of the table.
     * @return a new ResultSetMetaInfo object.
     * @throws SQLException
     */
    public ResultSetMetaInfo getResultSetMetaInfo(String table) throws SQLException {
        if (!this.resultSetMeta.containsKey(table)) {//columnInfo for specified table does not exist in memory
            /* calculate columnInfo */
            switch (this.engine) {
                case MYSQL: {
                    this.resultSetMeta.put(table, new MySqlResultSetMetaInfo(this, table));
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return resultSetMeta.get(table);
    }

    /**
     * Returns a ResultSetMetaInfo object containing information about the types and properties of the designated resutlset's columns.
     * @param rs the resultset.
     * @return a new ResultSetMetaInfo object.
     * @throws SQLException
     */
    public ResultSetMetaInfo getResultSetMetaInfo(ResultSet rs) throws SQLException {
        final String key = "rs." + String.valueOf(rs.hashCode());
        if (!this.resultSetMeta.containsKey(key)) {//columnInfo for specified table does not exist in memory
            /* calculate columnInfo */
            switch (this.engine) {
                case MYSQL: {
                    this.resultSetMeta.put(key, new MySqlResultSetMetaInfo(this, rs));
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return resultSetMeta.get(key);
    }

    /**
     * Returns an IO Controller for the designated column.
     * @param rsmi meta information for the resultset that the column belongs to.
     * @param columnIndex the position of the column in the resultset. The first column is 1, the second is 2, ...
     * @return
     * @throws SQLException
     */
    public IOController getIOController(ResultSetMetaInfo rsmi, int columnIndex) throws SQLException {
        switch (this.engine) {
            case MYSQL: {
                switch (rsmi.getDatatype(columnIndex)) {
                    case BIGINT: {
                        return new MySqlBigInt(rsmi, columnIndex);
                    }
                    case BINARY: {
                        return new MySqlBinary(rsmi, columnIndex);
                    }
                    case BIT: {
                        return new MySqlBit(rsmi, columnIndex);
                    }
                    case BLOB: {
                        return new MySqlBlob(rsmi, columnIndex);
                    }
                    case CHAR: {
                        return new MySqlChar(rsmi, columnIndex);
                    }
                    case DATE: {
                        return new MySqlDate(rsmi, columnIndex);
                    }
                    case DATETIME: {
                        return new MySqlDateTime(rsmi, columnIndex);
                    }
                    case DECIMAL: {
                        return new MySqlDecimal(rsmi, columnIndex);
                    }
                    case DOUBLE: {
                        return new MySqlDouble(rsmi, columnIndex);
                    }
                    case ENUM: {
                        return new MySqlEnum(rsmi, columnIndex);
                    }
                    case FLOAT: {
                        return new MySqlFloat(rsmi, columnIndex);
                    }
                    case INT: {
                        return new MySqlInt(rsmi, columnIndex);
                    }
                    case LONGBLOB: {
                        return new MySqlLongBlob(rsmi, columnIndex);
                    }
                    case LONGTEXT: {
                        return new MySqlLongText(rsmi, columnIndex);
                    }
                    case MEDIUMBLOB: {
                        return new MySqlMediumBlob(rsmi, columnIndex);
                    }
                    case MEDIUMINT: {
                        return new MySqlMediumInt(rsmi, columnIndex);
                    }
                    case MEDIUMTEXT: {
                        return new MySqlMediumText(rsmi, columnIndex);
                    }
                    case SET: {
                        return new MySqlSet(rsmi, columnIndex);
                    }
                    case SMALLINT: {
                        return new MySqlSmallInt(rsmi, columnIndex);
                    }
                    case TEXT: {
                        return new MySqlText(rsmi, columnIndex);
                    }
                    case TIME: {
                        return new MySqlTime(rsmi, columnIndex);
                    }
                    case TIMESTAMP: {
                        return new MySqlTimeStamp(rsmi, columnIndex);
                    }
                    case TINYBLOB: {
                        return new MySqlBlob(rsmi, columnIndex);
                    }
                    case TINYINT: {
                        return new MySqlTinyInt(rsmi, columnIndex);
                    }
                    case TINYTEXT: {
                        return new MySqlTinyText(rsmi, columnIndex);
                    }
                    case VARBINARY: {
                        return new MySqlVarBinary(rsmi, columnIndex);
                    }
                    case VARCHAR: {
                        return new MySqlVarChar(rsmi, columnIndex);
                    }
                    case YEAR: {
                        return new MySqlYear(rsmi, columnIndex);
                    }
                    default: {
                        return new MySqlChar(rsmi, columnIndex);
                    }
                }
            }
            case MSSQL: {
                /* coming soon */
                return null;
            }
            default: {
                return null;
            }
        }
    }

    /**
     * Returns an IO Controller for the designated column in the specified resultset.
     * @param rs the resultset.
     * @param columnIndex the position of the column in the resultset. The first column is 1, the second is 2, ...
     * @return
     * @throws SQLException
     */
    public IOController getIOController(ResultSet rs, int columnIndex) throws SQLException {
        ResultSetMetaInfo rsmi = this.getResultSetMetaInfo(rs);
        return this.getIOController(rsmi, columnIndex);
    }

    /**
     * Returns an IO Controller for the designated column of the specified table.
     * @param table the name of the column's parent table.
     * @param column the name of the column.
     * @return
     * @throws SQLException
     */
    public IOController getIOController(String table, String column) throws SQLException {
        ResultSetMetaInfo rsmi = this.getResultSetMetaInfo(table);
        return this.getIOController(rsmi, rsmi.getPosition(column));
    }

    /**
     * Returns a new SqlDesigner for this database.
     * @return
     */
    private SqlDesigner getSqlDesigner() {
        switch (this.engine) {
            case MYSQL: {
                return new MySqlDesigner();
            }
            case MSSQL: {
                return null;
            }
            default: {
                return null;
            }
        }
    }

    /**
     * Parses the input parameters and returns a new ResultSet.
     * @param query a restful query.
     * @return
     * @throws SQLException
     * @throws IOCException
     */
    private ResultSet getResultSet(RestfulQuery query) throws SQLException, IOCException {
        final String sql = getSqlDesigner().getSelectSQL(this, query.getColumns(), query.getTables(), query.getJoinOperations(), query.getFilterBoundaries(), query.getFilters(), query.getOrders(), query.getPageIndex(), query.getPageSize());
        final PreparedStatement ps = getPreparedStatement(sql);
        int index = 1;
        Filter[] filterBoundaries = query.getFilterBoundaries();
        for (int i = 0; i < filterBoundaries.length; i++) {//iterate filter boundaries
            Filter filter = filterBoundaries[i];
            this.getIOController(filter.getTable(), filter.getColumn()).setParameterValue(ps, index, filter.getValue());
            index++;//increase parameter index
        }
        Filter[] filters = query.getFilters();
        for (int i = 0; i < filters.length; i++) {//iterate filters
            Filter filter = filters[i];
            this.getIOController(filter.getTable(), filter.getColumn()).setParameterValue(ps, index, filter.getValue());
            index++;//increase parameter index
        }
        return ps.executeQuery();
    }

    /**
     * Parses the input parameters and returns a new ResultSet.
     * @param collection a String representing a combination of tables and join operations.
     * Symbol '+' in collection represents INNER JOIN operation.
     * Symbol '!+' in collection represents LEFT JOIN operation.
     * Symbol '+!' in collection represents RIGHT JOIN operation.
     * Symbol '!+!' in collection represents OUTER JOIN operation.
     * @param qs the supplied QueryString or the request URI.
     * @return
     * @throws SQLException
     * @throws QueryException
     * @throws IOCException
     */
    public ResultSet getResultSet(String collection, QueryString qs) throws SQLException, QueryException, IOCException {
        RestfulQuery query = new RestfulQuery(collection, qs).validate(this);
        return getResultSet(query);
    }

    /**
     * Returns an IO Controller array for the designated resultset.
     * @param rs the resultset upon which the array will be built.
     * @return
     * @throws SQLException
     */
    public IOController[] getIOControllerArray(ResultSet rs) throws SQLException {
        ResultSetMetaInfo rsmi = this.getResultSetMetaInfo(rs);
        final IOController[] ioc = new IOController[rsmi.getColumnCount()];
        for (int i = 1; i <= rsmi.getColumnCount(); i++) {//iterate columns
            ioc[i - 1] = this.getIOController(rsmi, i);
        }
        return ioc;
    }

    /**
     * Returns an IO Controller array for the designated table and columns.
     * @param table the table upon which the array will be built.
     * @param columns the columns upon which the array will be built.
     * @return
     * @throws SQLException
     */
    public IOController[] getIOControllerArray(String table, String[] columns) throws SQLException {
        ResultSetMetaInfo rsmi = this.getResultSetMetaInfo(table);
        final IOController[] ioc = new IOController[columns.length];
        for (int i = 0; i < columns.length; i++) {//iterate columns
            ioc[i] = this.getIOController(rsmi, rsmi.getPosition(columns[i]));
        }
        return ioc;
    }

    /**
     * Parses the input parameters and returns a XHTML table representation.
     * @param collection a String representing a combination of tables and join operations.
     * Symbol '+' in collection represents INNER JOIN operation.
     * Symbol '!+' in collection represents LEFT JOIN operation.
     * Symbol '+!' in collection represents RIGHT JOIN operation.
     * Symbol '!+!' in collection represents OUTER JOIN operation.
     * @param qs the supplied QueryString or the request URI.
     * @return
     * @throws SQLException
     * @throws QueryException
     * @throws IOCException
     * @throws RepresentationException
     */
    public XhtmlTable getXhtmlTable(String collection, QueryString qs) throws SQLException, QueryException, IOCException, RepresentationException {
        RestfulQuery query = new RestfulQuery(collection, qs).validate(this);
        ResultSet rs = getResultSet(query);
        return new XhtmlTable(this, rs);
    }

    /**
     * Parses the resultset and returns a XHTML table representation.
     * @param rs the resultset that will be used to generate this representation.
     * @return
     * @throws SQLException
     * @throws IOCException
     * @throws RepresentationException
     */
    public XhtmlTable getXhtmlTable(ResultSet rs) throws SQLException, IOCException, RepresentationException {
        return new XhtmlTable(this, rs);
    }

    /**
     * Parses the input parameters and returns a XHTML form representation.
     * @param collection a String representing a combination of tables and join operations.
     * Symbol '+' in collection represents INNER JOIN operation.
     * Symbol '!+' in collection represents LEFT JOIN operation.
     * Symbol '+!' in collection represents RIGHT JOIN operation.
     * Symbol '!+!' in collection represents OUTER JOIN operation.
     * @param qs the supplied QueryString or the request URI.
     * @return
     * @throws SQLException
     * @throws QueryException
     * @throws IOCException
     * @throws RepresentationException
     */
    public XhtmlForm getXhtmlForm(String collection, QueryString qs) throws SQLException, QueryException, IOCException, RepresentationException {
        RestfulQuery query = new RestfulQuery(collection, qs).validate(this);
        if (query.getTableCount() == 1) {//query contains only one (1) table
            XhtmlForm doc = new XhtmlForm(this, getResultSet(query));
            Filter[] filters = query.getFilterBoundaries();
            for (int i = 0; i < filters.length; i++) {//iterate filter boundaries
                Filter f = filters[i];//get current filter
                doc.setLock(f.getTable(), f.getColumn(), f.getValue());
            }
            return doc;
        } else {//query contains more than one (1) tables
            throw new RepresentationException("Cannot generate XHTML form. More than one tables are specified.");
        }
    }

    /**
     * Parses the input parameters and returns an empty XHTML form that can be submitted for creating a new resource.
     * @param collection a String representing a combination of tables and join operations.
     * Symbol '+' in collection represents INNER JOIN operation.
     * Symbol '!+' in collection represents LEFT JOIN operation.
     * Symbol '+!' in collection represents RIGHT JOIN operation.
     * Symbol '!+!' in collection represents OUTER JOIN operation.
     * @return
     * @throws SQLException
     * @throws QueryException
     * @throws IOCException
     * @throws RepresentationException
     */
    public XhtmlForm getEmptyXhtmlForm(String collection) throws SQLException, QueryException, IOCException, RepresentationException {
        RestfulQuery query = new RestfulQuery(collection).validate(this);
        if (query.getTableCount() == 1) {//query contains only one (1) table
            final String table = query.getTables()[0];//get the first table in tables array
            final String[] columns = query.getColumns(table);//get the columns of the query that belong to this table
            XhtmlForm doc = new XhtmlForm(this, table, columns);
            Filter[] filters = query.getFilterBoundaries();
            for (int i = 0; i < filters.length; i++) {//iterate filter boundaries
                Filter f = filters[i];//get current filter
                doc.setLock(f.getTable(), f.getColumn(), f.getValue());
            }
            return doc;
        } else {//query contains more than one (1) tables
            throw new RepresentationException("Cannot generate XHTML form. More than one tables are specified.");
        }
    }

    /**
     * Parses the input parameters and sets a new record in this database.
     * @param collection a String representing a combination of tables and join operations.
     * In this case only one table must be contained in the collection. If otherwise, a SQLException is raised.
     * @param form the content of the HTTP request encoded as multipart/form-data.
     * @return
     * @throws SQLException
     * @throws QueryException
     * @throws IOCException
     */
    public boolean setNew(String collection, MimeMultipart form) throws SQLException, QueryException, IOCException {
        RestfulQuery query = new RestfulQuery(collection).validate(this);
        if (query.getTableCount() == 1) {//query contains only one (1) table
            final MultipartFormData data = new MultipartFormData(form);
            final String table = query.getTables()[0];
            ResultSetMetaInfo rsmi = this.getResultSetMetaInfo(table);
            final String[] columns = query.getColumns(table);
            final IOController[] ioc = this.getIOControllerArray(table, columns);
            final String sql = getSqlDesigner().getInsertSQL(this, columns, table, query.getFilterBoundaries());
            final PreparedStatement ps = getPreparedStatement(sql);
            int index = 1;
            for (int i = 0; i < ioc.length; i++) {//iterate io controllers
                if (!rsmi.isAutoIncremented(columns[i])) {//current column is not auto incremented
                    final String id = ioc[i].getId();
                    ioc[i].setParameterValue(ps, i, data.getFirst(id));
                    index++;//increase parameter index
                }
            }
            final boolean result = ps.execute();
            ps.close();
            return result;
        } else {//query contains more than one (1) tables
            final SqlExceptionState state = SqlExceptionState.INSERT_INTO_MULTIPLE_TABLES;
            throw new SQLException("Cannot set new record. More than one tables are specified.", state.name(), state.code());
        }
    }

    /**
     * Parses the input parameters and sets a new record in this database.
     * @param collection a String representing a combination of tables and join operations.
     * In this case only one table must be contained in the collection. If otherwise, a SQLException is raised.
     * @param instance
     * @param form the content of the HTTP request encoded as multipart/form-data.
     * @return
     * @throws SQLException
     * @throws QueryException
     * @throws IOCException
     */
    public boolean set(String collection, String instance, MimeMultipart form) throws SQLException, QueryException, IOCException {
        return true;
    }
}
